from common import ApplicationEntity,SearchFilter
import DatabaseConnection

class ApplicationData(object):
    def __init__(self):
        pass

    def InsertApplication(self,application):
        ID = None
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('InsApplication',(application.ApplicationID,application.Owner,application.ApplicationType,application.Resume,\
                                          application.Decision,application.DateSubmitted,application.DateDecided,application.Reviewer))
        ID = cursor.fetchone()[0]
        cursor.close()
        conn.commit()
        conn.close()
        return ID

    def UpdateApplication(self,application):
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('UpdApplication',(application.ApplicationID,application.Owner,application.ApplicationType,application.Resume,\
                                          application.Decision,application.DateSubmitted,application.DateDecided,application.Reviewer))
        cursor.close()
        conn.commit()
        conn.close()

    def DeleteApplication(self,ID):
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('DelApplication',(ID,))
        cursor.close()
        conn.commit()
        conn.close()

    #This method either return an object of type ApplicationEntity or None (if there is no record with this ID)
    def SelectApplicationByID(self,ID):
        query = """ SELECT ApplicationID,Owner,ApplicationType,Resume,Decision,DateSubmitted,DateDecided,Reviewer,OwnerName,ApplicationTypeDesc,DecisionDesc,ReviewerName
                    FROM vwApplication
                    WHERE ApplicationID='%s'""" % ID

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        if len(result) <> 0:
            return ApplicationEntity.ApplicationEntity(applicationID=result[0][0],owner=result[0][1],applicationType=result[0][2],decision=result[0][3],resume=result[0][4],dateSubmitted=result[0][5],dateDecided=result[0][6],reviewer=result[0][7],ownerName=result[0][8],applicationTypeDesc=result[0][9],decisionDesc=result[0][10],reviewName=result[0][11])
        else:
            return None

    #This method returns a list of objects of type ApplicationEntity
    def SelectApplicationByFilter(self,searchFilter):
        query = """ SELECT ApplicationID,Owner,ApplicationType,Resume,Decision,DateSubmitted,DateDecided,Reviewer,OwnerName,ApplicationTypeDesc,DecisionDesc,ReviewerName
                    FROM vwApplication"""

        whereClause = searchFilter.GetWhereClause()
        if whereClause.strip()<>"":
            query += " WHERE %s" % whereClause

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        recordSet = []
        for record in result:
            recordSet.append(ApplicationEntity.ApplicationEntity(applicationID=record[0],owner=record[1],applicationType=record[2],decision=record[3],\
                                                                 resume=record[4],dateSubmitted=record[5],dateDecided=record[6],reviewer=record[7],\
                                                                 ownerName=record[8],applicationTypeDesc=record[9],decisionDesc=record[10],reviewName=record[11]))
        return recordSet

    def SelectAllApplication(self):
        query = """ SELECT ApplicationID,Owner,ApplicationType,Resume,Decision,DateSubmitted,DateDecided,Reviewer,OwnerName,ApplicationTypeDesc,DecisionDesc,ReviewerName
                    FROM vwApplication"""

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        recordSet = []
        for record in result:
            recordSet.append(ApplicationEntity.ApplicationEntity(applicationID=record[0],owner=record[1],applicationType=record[2],decision=record[3],\
                                                                 resume=record[4],dateSubmitted=record[5],dateDecided=record[6],reviewer=record[7],\
                                                                 ownerName=record[8],applicationTypeDesc=record[9],decisionDesc=record[10],reviewName=record[11]))
        return recordSet
